using System;
using System.Data;
using System.IO;
using System.Collections.Generic;
using System.Text;


namespace LibHuman
{
    public class modify
    {
        private AccessData mDb;
        DataSet ds;

        public modify()
        {
            mDb = new AccessData();
        }

        public void modify_data()
        {
            ds = new DataSet();
            ds.ReadXml("..\\xml\\modify.xml");

            foreach(DataRow dr in ds.Tables[0].Rows)
            {
                mDb.Execute_data(dr["sql"].ToString());
            }
        }

        private string[] sqlmodify()
        {
            string[] _sql ={"CREATE TABLE nhansu.khenthuong (manv varchar(6),nam numeric(10) DEFAULT 0,madv varchar(3), maloai varchar(2),daunam bool DEFAULT false,cuoinam bool DEFAULT false,canam bool DEFAULT false, so_kt bool DEFAULT false,cs_kt bool DEFAULT false,ub_kt bool DEFAULT false,ghichu text,ngayud timestamp DEFAULT now(),constraint khenthuong_pk primary key (manv,nam))",
                "CREATE TABLE nhansu.khenthuong_dv( madv varchar(3),nam numeric(10) DEFAULT 0,so_kt bool DEFAULT false, cs_kt bool DEFAULT false, ub_kt bool DEFAULT false, ghichu text, ngayud timestamp DEFAULT now(),constraint khenthuong_pk primary key (madv,nam))",
                "CREATE TABLE nhansu.dmnhiemvu (manv varchar(5) , yyyy varchar(4),nam numeric(6,2), ghichu text, CONSTRAINT dmnhiemvu_pkey PRIMARY KEY (manv, yyyy) )",
                "alter table nhansu.giacanh rename column ghichu to quequan","alter table nhansu.giacanh add column nghenghiep text","alter table nhansu.nhombc add column title text",
                "alter table nhansu.nhombc add column title1 text","alter table nhansu.dmcd add column tenvt text","alter table nhansu.dmcv add column tenvt text",
                "alter table nhansu.dmcv add column lanhdao bool default false","alter table nhansu.ctdaotao add column tenvt varchar(20)","alter table nhansu.bophan add column sothutu number(10,0) default 0",
                "alter table nhansu.bophan add column sogiuong number(10,0) default 0","alter table nhansu.bophan add column tenvt varchar(20)","alter table nhansu.llnv add column namtn_cm timestamp",
                "alter table nhansu.llnv add column msthue varchar(20)","alter table nhansu.hopdong alter column todoan type varchar(254)","alter table nhansu.hopdong alter column to_t type varchar(254)",
                "alter table nhansu.hopdong alter column chi_bo type varchar(254)","alter table nhansu.hopdong add column bhtn number(6,3)","alter table luong.bcc add column t_bhtn number(10,3)",
                "alter table luong.nv add column bhtn number(1,0) default 0","alter table luong.heso add column mbhtn_hs number(10,2) default 0","alter table luong.heso add column mtietkiem_hs number(10,2) default 0",
                "alter table luong.bcc add column t_tk number(10,2) default 0","CREATE TABLE luong.thunhap( manv character varying(5),  mmyy character varying(4),  madv character varying(3),  t_luong numeric(10,2),  t_pcvuotkhung numeric(10,1),  t_pcchucvu numeric(10,2),  t_pctrachnhiem numeric(10,2),  t_pclaynhiem numeric(10,2),  t_pcudnghe numeric(10,2),  t_pcbdhv numeric(10,2),  t_ngoaigio numeric(10,2),  t_pctrucchuyenmon numeric(10,2),  t_pcabc numeric(10,2),  t_pcphauthuat numeric(10,2),  t_trunghi numeric(10,2),  t_bhxh numeric(10,2),  t_bhyt numeric(10,2),  ghichu character varying(2000),  done boolean DEFAULT false,  edit numeric(10) DEFAULT 0,  ngayud timestamp without time zone DEFAULT now(),  t_thkhac numeric(10,2) DEFAULT 0,  CONSTRAINT thunhap_pkey PRIMARY KEY (manv, mmyy))",
                "CREATE OR REPLACE VIEW nhansu.gcanh_me AS SELECT giacanh.manv, giacanh.madt, giacanh.htendtg, giacanh.namsinh, giacanh.quequan AS ghichu, doituong.tendt  FROM nhansu.giacanh   JOIN nhansu.doituong ON giacanh.madt = doituong.madt  WHERE giacanh.madt = '06'::text ORDER BY giacanh.manv, giacanh.madt",
                "CREATE OR REPLACE VIEW nhansu.gcanh_cha AS  SELECT giacanh.manv, giacanh.madt, giacanh.htendtg, giacanh.namsinh, giacanh.quequan AS ghichu, doituong.tendt   FROM nhansu.giacanh   JOIN nhansu.doituong ON giacanh.madt = doituong.madt  WHERE giacanh.madt = '05'::text  ORDER BY giacanh.manv, giacanh.madt",
                "CREATE OR REPLACE VIEW nhansu.v_tinhbhyt AS  SELECT l.manv, tt.tentt  FROM nhansu.btdtt tt  LEFT JOIN nhansu.thebhyt yt ON tt.matt::text = yt.captai::text   JOIN nhansu.llnv l ON l.manv::text = yt.manv::text",
                "create table nhansu.nangluongtam (manv varchar(5) primary key,hoten varchar(254),phai varchar(10),ngaysinh timestamp,namsinh number(4,0),madv varchar(3),tenbp varchar(50),tenvt varchar(20),mangach varchar(8),loaingach varchar(4),b_dau number(2,0),hs_lc number(6,3),hs_vk number(6,2),ngaybnngach timestamp,bac number(2,0),hs_lcmoi number(6,3),luonghd number(10,0),ngaynang timestamp,ngay_ud timestamp default now())",
                "update nhansu.mangach set loaingach='A0' where ma in ('15.113','17.147')",
                "update nhansu.mangach set loaingach='A1' where ma in ('14.105','14.106','15.111','16.118','13.092','16.134','17.144','02.014','09.056','09.060','09.064','09.072','12.089','13.095','13.099','13.102','16.120','16.123','16.126','17.141','17.152','17.156','17.162','17.166','17.170','17.175','17.177','18.181','15.113','17.150','17.174','17a.193','17a.196','10.078','03.018','04.025','06.043','08.051','01.003','03.019','06.038','07.046','21.189','06.031','09.068','12.086','11.082')",
                "update nhansu.mangach set loaingach='A2' where ma in ('13.091','15.110','16.117','16.113','14.104','12.088','13.094','13.098','13.101','17.140','17.143','17.155','17.161','18.180','09.055','09.059','09.063','09.071','15.112','02.013','17.146','17.151','17.158','17.165','17.169','17.149','17.173','17a.192','17a.195')",
                "update nhansu.mangach set loaingach='A3' where ma in ('13.090','15.109','16.116','16.132','17.139','17.142','17.154','17.157','18.179','12.087','13.093','13.097','13.100','14.103','17.160','17.145','02.012','17.164','17.168','09.054','09.058','09.062','09.070','17.148','17.172','17a.191','17a.194')",
                "update nhansu.mangach set loaingach='B' where ma in ('10.079','08.052','19.183','06.039','11.083','07.048','01.004','06.032','09.069','21.190')",
                "update nhansu.mangach set loaingach='B'where ma in ('14.107','17.159','15.114','16.119','16.121','16.124','16.127','18.182','02.015','02.016','09.057','09.061','09.065','09.073','13.096','16.135','16.137','17.153','17.163','17.167','17.171','17.176','17.178','15.115')",
                "update nhansu.mangach set loaingach='C'	where ma in ('10.080','08.053','19.184','19.185','19.186','06.034','07.047','06.035','06.040','06.033')",
                "update nhansu.mangach set loaingach='C' where ma in ('14.108','16.122','16.125','16.128','16.130','16.136','16.138','16.131','16.129')",
                "update nhansu.mangach set loaingach='C' where ma in ('01.010','01.005','01.007','01.006','01.011','01.008','01.009')",                
                "alter table nhansu.giacanh add column ghichu text","alter table nhansu.bangcap add column stt numeric(10,0)","alter table nhansu.dmnhomctdt add column mabc varchar(2)",
                "create table nhansu.bangcapbc (mabang varchar(2),tenbang varchar(254),	constraint bangcapbc_pk primary key (mabang))","alter table luong.bcc add column l_khoan numeric(10,0) default 0",
                "alter table luong.bcc add column k_buoi numeric(10,2) default 0","alter table luong.bcc add column k_ngay numeric(10,2) default 0","alter table luong.heso add column mkngay_hs numeric(10,0) default 0","alter table luong.heso add column mkbuoi_hs numeric(10,0) default 0",
                "alter table luong.nn add column nghi_bu numeric(10,2) default 0","alter table luong.nn add column nghi_kt numeric(10,2) default 0",
                "alter table luong.nn add column nghi_sc numeric(10,2) default 0","alter table luong.nn add column nghi_khac numeric(10,2) default 0",
                "alter table luong.pc add column madv1 varchar(3)","alter table luong.pc add column thuchien numeric(1,0) default 0",
                "create table public.pg_comment (id serial primary key,all_right bytea,user_ varchar(1) default 'H',date_all_right timestamp default now())",
                " alter table nhansu.llnv add column ngaychuyendi_dang timestamp",
                " alter table nhansu.llnv add column noichuyendi_dang text",
                " alter table nhansu.llnv add column noichuyenden_dang text"            

            };
            return _sql;
        }
    }
}
